﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using NPOI;
using NPOI.OpenXml4Net;
using NPOI.OpenXmlFormats;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using System.IO;
using NLog;
using System.Diagnostics;

namespace WebApplication1.Common
{
    public class Common
    {

        public  static Logger log = NLog.LogManager.GetCurrentClassLogger();

        private Common()
        {

        }
        /// <summary>
        /// 将DataTable中的数据以Excel文件的形式输出到Http Response
        /// ToDo:       行、单元格设置样式
        /// </summary>
        /// <param name="fileName">Excel文件名</param>
        /// <param name="headerString">Excel标题</param>
        /// <param name="dataTable">数据源</param>
        public static void PrintExcel(string fileName, string headerString, DataTable dataTable)
        {
            Stopwatch sw = new Stopwatch();
            sw.Start();

            //1.创建excel worksheet
            HSSFWorkbook wb = new HSSFWorkbook();
            HSSFSheet sheet1 = wb.CreateSheet("sheet1") as HSSFSheet;
            HSSFRow row;
            //2.设置header
             row= sheet1.CreateRow(0) as HSSFRow;
            row.CreateCell(0).SetCellValue(headerString);
            //3.填充数据
            if (dataTable != null)
            {
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    row = sheet1.CreateRow(i + 1) as HSSFRow;
                    DataRow dr=dataTable.Rows[i];
                    for (int j = 0;j< dr.ItemArray.Count(); j++)
                    {
                        row.CreateCell(j).SetCellValue(dr[j].ToString());                             
                    }
                }
            }
            //4.输出到Response
            MemoryStream ms = new MemoryStream();
            wb.Write(ms);
            wb.Close();

            #region " 输出到Response "
            
            HttpContext httpcontext =HttpContext.Current;
            if (httpcontext != null) 
            {
                httpcontext.Response.Headers["Content-Disposition"] = "attachment;filename="+fileName;
                httpcontext.Response.ContentType = "application/ms-excel"; 
                httpcontext.Response.BinaryWrite(ms.ToArray());
                //httpcontext.Response.End();
                ms.Close();
            }            

            #endregion
            sw.Stop();
            log.Log(LogLevel.Info, "PrinfExcel costs" + sw.Elapsed.ToString());
        }
    }
}